/*
 Navicat Premium Data Transfer

 Source Server         : mysql
 Source Server Type    : MySQL
 Source Server Version : 80011
 Source Host           : localhost:3306
 Source Schema         : training

 Target Server Type    : MySQL
 Target Server Version : 80011
 File Encoding         : 65001

 Date: 21/06/2018 10:43:19
*/

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

CREATE SCHEMA IF NOT EXISTS `training` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci ;
USE `training` ;

-- ----------------------------
-- Table structure for class
-- ----------------------------
DROP TABLE IF EXISTS `class`;
CREATE TABLE `class` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '培训课程id',
  `subject` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '培训主题',
  `user_id` bigint(20) NOT NULL COMMENT '创建人id',
  `user_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '创建人姓名',
  `dr` tinyint(4) NOT NULL DEFAULT '0' COMMENT '删除标示',
  `teacher_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '讲师姓名',
  `teacher_id` bigint(20) NOT NULL COMMENT '讲师id',
  `sys_created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `sys_modified_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `assistant_id` bigint(20) NOT NULL COMMENT '助教id',
  `assistant_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '助教姓名',
  `filepath` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '附件下载地址',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Table structure for class_student
-- ----------------------------
DROP TABLE IF EXISTS `class_student`;
CREATE TABLE `class_student` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '流水号',
  `class_id` bigint(20) NOT NULL COMMENT '课程id',
  `user_id` bigint(20) NOT NULL COMMENT '参训员工id',
  `user_name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '参训员工姓名',
  `applicant_id` bigint(20) NOT NULL COMMENT '报名人id',
  `sys_created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `sys_modified_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `signstate` tinyint(4) NOT NULL DEFAULT '0' COMMENT '签到状态，0未签到，1已签到',
  `address` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '签到地址',
  `latitude` decimal(6,0) DEFAULT NULL COMMENT '纬度',
  `longitude` decimal(6,0) DEFAULT NULL COMMENT '经度',
  `leavestate` tinyint(4) NOT NULL DEFAULT '0' COMMENT '请假状态，0未请假，1请假，默认为0',
  `assess_id` bigint(20) DEFAULT NULL COMMENT '评价信息id，一对一',
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Table structure for class_sub
-- ----------------------------
DROP TABLE IF EXISTS `class_sub`;
CREATE TABLE `class_sub` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '子表课程id',
  `starttime` datetime NOT NULL COMMENT '开始时间',
  `endtime` datetime NOT NULL COMMENT '结束时间',
  `address` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL COMMENT '课程培训地点',
  `dr` tinyint(4) NOT NULL DEFAULT '0' COMMENT '删除标示',
  `class_id` bigint(20) unsigned NOT NULL COMMENT '培训课程主表id',
  `sys_created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `sys_modified_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

-- ----------------------------
-- Table structure for user
-- ----------------------------
DROP TABLE IF EXISTS `user`;
CREATE TABLE `user` (
  `id` bigint(20) unsigned NOT NULL COMMENT '用户id',
  `dr` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否已删除',
  `sys_created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `sys_modified_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  `dept_id` bigint(20) unsigned DEFAULT NULL COMMENT '所属部门',
  `dept_name` varchar(100) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '所属部门',
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '姓名',
  `staff_no` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '员工号',
  `mobile` varchar(30) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '手机号',
  `email` varchar(128) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL COMMENT '邮箱',
  `sex` varchar(4) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT '' COMMENT '性别，1男2女',
  `password` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '密码',
  `avatar` varchar(200) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT '' COMMENT '头像主键',
  `type` tinyint(3) unsigned NOT NULL DEFAULT '2' COMMENT '1:系统管理员,2:用户,默认为用户',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
-- ----------------------------
-- Records of user
-- ----------------------------
BEGIN;
INSERT INTO `user` VALUES (99999999999, 0, '2018-06-21 10:40:08', NULL, NULL, NULL, NULL, NULL, 'admin', NULL, '', '263c47dae369bd1741b6dc0a43c4cd7b2731dee08762c3b1cbbef0b087aa5cbb902fec7b20d238c75578be9a12c9101822ca54d7969db8dfa8e83482f1cff606d82ec2d300d8a02c60c3489f08cfe1c8', '', 1);
COMMIT;
-- ----------------------------
-- Table structure for user_auth
-- ----------------------------
DROP TABLE IF EXISTS `user_auth`;
CREATE TABLE `user_auth` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT '姓名',
  `ispublish` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否可发布课程0否1是',
  `issignup` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否可代报名0否1是',
  `isreport` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否可查看报表0否1是',
  `dr` tinyint(4) NOT NULL DEFAULT '0' COMMENT '是否已删除',
  `sys_created_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `sys_modified_time` datetime DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1010107 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

ALTER TABLE `user` DROP field;
ALTER TABLE `user` ADD workstatus VARCHAR(50) NOT NULL DEFAULT '' COMMENT '在职状态';
ALTER TABLE `user` ADD filed VARCHAR(50) NOT NULL DEFAULT '' COMMENT '领域';


ALTER TABLE `class_student` ADD subclass_id bigint(20) NOT NULL DEFAULT 0 COMMENT '子课程id';
ALTER TABLE `class_student` DROP latitude;
ALTER TABLE `class_student` DROP longitude;
ALTER TABLE `class_student` ADD latitude decimal(15,12) DEFAULT NULL COMMENT '纬度';
ALTER TABLE `class_student` ADD longitude decimal(15,12) DEFAULT NULL COMMENT '经度';

SET FOREIGN_KEY_CHECKS = 1;



ALTER TABLE `class_student` ADD dr TINYINT(4) NOT NULL DEFAULT 0 COMMENT '删除标识';

ALTER TABLE `user` DROP join_date;
ALTER TABLE `user` ADD join_date datetime DEFAULT NULL  COMMENT '入职时间';